using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Text;
namespace DAL
{
    public class Quotes : DAL.DataManager<Quote>
    {
        private static Quotes Instance = new Quotes();
        static Quotes() { }

        public override string TableName
        {
            get { return @"fstdt_quotes"; }
        }

        protected override void CopyToItem(DataRow Row, Quote Item)
        {
            Item.Fundie = Convert.ToString(Row["Fundie"]);
            Item.FundieQuote = Convert.ToString(Row["Quote"]);
            Item.URL = Convert.ToString(Row["URL"]);
            Item.Board = Convert.ToString(Row["Board"]);


            if (Row["Contributor"] != System.DBNull.Value)
            {
                Item.Contributor = Convert.ToString(Row["Contributor"]);
            }
            else
            {
                Item.Contributor = null;
            }


            if (Row["ContributorEmail"] != System.DBNull.Value)
            {
                Item.ContributorEmail = Convert.ToString(Row["ContributorEmail"]);
            }
            else
            {
                Item.ContributorEmail = null;
            }


            Item.CreditContributor = Convert.ToBoolean(Row["CreditContributor"]);
            Item.TotalComments = Convert.ToInt32(Row["TotalComments"]);
            Item.TotalVotes = Convert.ToInt32(Row["TotalVotes"]);
            Item.NumberOfVoters = Convert.ToInt32(Row["NumberOfVoters"]);


            switch (Convert.ToInt32(Row["ApprovalStatus"]))
            {
                case 0:
                    Item.ApprovalStatus = Quote.QuoteStatus.Pending;
                    break;
                case 1:
                    Item.ApprovalStatus = Quote.QuoteStatus.Approved;
                    break;
                default:
                    Item.ApprovalStatus = Quote.QuoteStatus.Deleted;
                    break;
            }


            Item.DateSubmitted = Convert.ToDateTime(Row["DateSubmitted"]);


            if (Row["DatePosted"] != System.DBNull.Value)
            {
                Item.DatePosted = Convert.ToDateTime(Row["DatePosted"]);
            }
            else
            {
                Item.DatePosted = null;
            }

            switch (Convert.ToInt32(Row["ThreadStatus"]))
            {
                case 1:
                    Item.CommentState = Quote.CommentStatus.Open;
                    break;
                default:
                    Item.CommentState = Quote.CommentStatus.Closed;
                    break;
            }

            Item.ApproveVotes = Convert.ToInt32(Row["ApproveVotes"]);

            Item.DeleteVotes = Convert.ToInt32(Row["DeleteVotes"]);

            Item.Archive = Archives.Item(Convert.ToInt32(Row["ArchiveID"]));

            if (Row["LatestCommentID"] != System.DBNull.Value)
            {
                Item.LatestComment = Comments.Item(Convert.ToInt32(Row["LatestCommentID"]));
            }
            else
            {
                Item.LatestComment = null;
            }
        }

        protected override void CopyFromItem(DataManagerQuery query, Quote Item)
        {
            query.AddParam("Fundie", Item.Fundie);
            query.AddParam("Quote", Item.FundieQuote);
            query.AddParam("URL", Item.URL);
            query.AddParam("Board", Item.Board);

            if (Item.Contributor != null)
            {
                query.AddParam("Contributor", Item.Contributor);
            }
            else
            {
                query.AddParam("Contributor", System.DBNull.Value);
            }

            if (Item.ContributorEmail != null)
            {
                query.AddParam("ContributorEmail", Item.ContributorEmail);
            }
            else
            {
                query.AddParam("ContributorEmail", System.DBNull.Value);
            }


            query.AddParam("CreditContributor", Item.CreditContributor);
            query.AddParam("TotalComments", Item.TotalComments);
            query.AddParam("TotalVotes", Item.TotalVotes);
            query.AddParam("NumberOfVoters", Item.NumberOfVoters);


            switch (Item.ApprovalStatus)
            {
                case Quote.QuoteStatus.Approved:
                    query.AddParam("ApprovalStatus", 1);
                    break;
                case Quote.QuoteStatus.Pending:
                    query.AddParam("ApprovalStatus", 0);
                    break;
                default:
                    query.AddParam("ApprovalStatus", -1);
                    break;
            }


            query.AddParam("DateSubmitted", Item.DateSubmitted);


            if (Item.DatePosted.HasValue)
            {
                query.AddParam("DatePosted", Item.DatePosted.Value);
            }
            else
            {
                query.AddParam("DatePosted", System.DBNull.Value);
            }


            switch (Item.CommentState)
            {
                case Quote.CommentStatus.Open:
                    query.AddParam("ThreadStatus", 1);
                    break;
                default:
                    query.AddParam("ThreadStatus", 0);
                    break;
            }


            query.AddParam("ApproveVotes", Item.ApproveVotes);
            query.AddParam("DeleteVotes", Item.DeleteVotes);
            query.AddParam("ArchiveID", Item.Archive.ID);

            if (Item.LatestComment != null)
            {
                query.AddParam("LatestCommentID", Item.LatestComment.ID);
            }
            else
            {
                query.AddParam("LatestCommentID", null);
            }
        }

        public static Quote Item(Int64 id)
        {
            return Instance.GetItem(id);
        }

        public static List<Quote> All()
        {
            return Instance.GetItems();
        }

        public static void Add(Quote Item)
        {
            Item.DateSubmitted = DateTime.Now;
            Item.CommentState = Quote.CommentStatus.Open;
            Item.ApprovalStatus = Quote.QuoteStatus.Pending;
            Instance.Insert(Item);
        }

        public static void Save(Quote Item)
        {
            Instance.Update(Item);
        }

        public static DataManagerPage<Quote> GetQuotesByMonth(Archive archive, DateTime archiveDate, int page)
        {
            DataManagerQuery query = new DataManagerQuery();
            DateTime startDate = archiveDate.AddDays(archiveDate.Day * -1).AddDays(1);
            DateTime endDate = archiveDate.AddMonths(1).AddDays(archiveDate.Day * -1);

            if (archive != null)
            {
                query.AddParam("ArchiveID", archive.ID);
            }
            else
            {
                query.AddParam("ArchiveID", 0);
            }
            query.AddParam("DatePosted", Comparison.GreaterThanOrEqual, startDate);
            query.AddParam("DatePosted", Comparison.LessThanOrEqual, endDate);
            query.AddParam("ApprovalStatus", 1);
            query.AddSort("DatePosted", Sort.Desc);
            return Instance.GetItemsByPage(query, page, 25);
        }

        public static DataManagerPage<Quote> GetLatestQuotesByArchiveAndPage(Archive archive, int page)
        {
            DataManagerQuery query = new DataManagerQuery();
            query.AddParam("ArchiveID", archive.ID);
            query.AddSort("LatestCommentID", Sort.Desc);
            return Instance.GetItemsByPage(query, page, 25);
        }

        public static Quote GetRandomUnapprovedQuote(User user)
        {
            //getting unapproved quotes which have not been approved by user

            if (user != null)
            {
                System.Text.StringBuilder sb = new System.Text.StringBuilder();
                sb.AppendFormat("SELECT TOP 1 * FROM {0} q ", Quotes.Instance.TableName).AppendLine();
                sb.AppendFormat("LEFT JOIN {0} pa ON q.ID = pa.QuoteID and pa.UserID = @userID", PublicAdmins.GetTableName()).AppendLine();
                sb.AppendLine("WHERE q.ApprovalStatus = 0 AND");
                sb.AppendLine("pa.ID is null");
                sb.AppendLine("ORDER BY newID()");
                string sql = sb.ToString();

                System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand(sql);
                comm.Parameters.AddWithValue("@userID", user.ID);

                List<Quote> myQuotes = Instance.GetBySqlCommand(comm);
                if (myQuotes.Count > 0)
                {
                    return myQuotes[0];
                }
                else
                {
                    return null;
                }
            }
            else
            {
                return null;
            }
        }

        public static List<Quote> GetRandomQuotes(Archive archive)
        {
            string sql = null;
            System.Data.SqlClient.SqlCommand comm = null;
            if (archive != null)
            {
                sql = String.Format("SELECT Top 25 * FROM [{0}] where ApprovalStatus = 1 and ArchiveID = @archiveID ORDER BY newid()", Quotes.Instance.TableName);
                comm = new System.Data.SqlClient.SqlCommand(sql);
                comm.Parameters.AddWithValue("@archiveID", archive.ID);
            }
            else
            {
                sql = String.Format("SELECT Top 25 * FROM [{0}] where ApprovalStatus = 1 ORDER BY newid()", Quotes.Instance.TableName);
                comm = new System.Data.SqlClient.SqlCommand(sql);
            }
            return Instance.GetBySqlCommand(comm);
        }

        public static int GetTotalPublishedQuotes()
        {
            DataManagerQuery query = new DataManagerQuery();
            query.AddParam("ArchiveID", 1);
            query.AddParam("ApprovalStatus", 1);
            return Instance.GetCount(query);
        }

        public static List<Quote> GetTop100QuotesByArchive(Archive archive)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("declare @C float");
            sb.AppendLine("select @C = cast(sum(TotalVotes) as float) / cast(sum(NumberOfVoters) as float) from fstdt_quotes");
            sb.AppendLine("WHERE ArchiveID = @archiveID");
            sb.AppendLine("declare @M float");
            sb.AppendLine("set @M = 30.0");
            sb.AppendLine("select Top 100 (NumberOfVoters / (NumberOfVoters + @M)) * ");
	        sb.AppendLine(" cast((TotalVotes / NumberOfVoters) as float) + ");
            sb.AppendLine(" (@M / (NumberOfVoters + @M)) * @C as WeightedAverage, *");
            sb.AppendLine("FROM fstdt_quotes");
            sb.AppendLine("WHERE NumberOfVoters >= @M");
            sb.AppendLine("AND ArchiveID = @archiveID");
            sb.AppendLine("ORDER BY WeightedAverage DESC");

            System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand(sb.ToString());
            comm.Parameters.AddWithValue("@archiveID", archive.ID);
            return Instance.GetBySqlCommand(comm);
        }

        public static DataManagerPage<Quote> Search(string fundie, string quote, string board, int page)
        {
            fundie = Helpers.FullTextHelper.GetFullTextSearchParam(fundie);
            quote = Helpers.FullTextHelper.GetFullTextSearchParam(quote);
            board = Helpers.FullTextHelper.GetFullTextSearchParam(board);

            DataManagerQuery query = new DataManagerQuery();
            if (!string.IsNullOrEmpty(fundie))
            {
                query.AddParam("fundie", Comparison.Contains, fundie);
            }

            if (!string.IsNullOrEmpty(quote))
            {
                query.AddParam("quote", Comparison.Contains, quote);
            }
            
            if (!string.IsNullOrEmpty(board))
            {
                query.AddParam("board", Comparison.Contains, board);
            }

            query.AddParam("ApprovalStatus", 1);
            query.AddSort("ID", Sort.Desc);

            return Instance.GetItemsByPage(query, page, 25);
        }
    }

    public class Quote : DAL.DataManagerRow
    {
        public enum QuoteStatus
        {
            Approved,
            Deleted,
            Pending
        }

        public enum CommentStatus
        {
            Open,
            Closed
        }


        private string _Fundie;
        public string Fundie
        {
            get
            {
                return _Fundie;
            }
            set
            {
                _Fundie = value;
            }
        }


        private string _Quote;
        public string FundieQuote
        {
            get
            {
                return _Quote;
            }
            set
            {
                _Quote = value;
            }
        }


        private string _URL;
        public string URL
        {
            get
            {
                return _URL;
            }
            set
            {
                _URL = value;
            }
        }


        private string _Board;
        public string Board
        {
            get
            {
                return _Board;
            }
            set
            {
                _Board = value;
            }
        }


        private string _Contributor;
        public string Contributor
        {
            get
            {
                return _Contributor;
            }
            set
            {
                _Contributor = value;
            }
        }


        private string _ContributorEmail;
        public string ContributorEmail
        {
            get
            {
                return _ContributorEmail;
            }
            set
            {
                _ContributorEmail = value;
            }
        }


        private bool _CreditContributor;
        public bool CreditContributor
        {
            get
            {
                return _CreditContributor;
            }
            set
            {
                _CreditContributor = value;
            }
        }


        private Int32 _TotalComments;
        public Int32 TotalComments
        {
            get
            {
                return _TotalComments;
            }
            set
            {
                _TotalComments = value;
            }
        }


        private Int32 _TotalVotes;
        public Int32 TotalVotes
        {
            get
            {
                return _TotalVotes;
            }
            set
            {
                _TotalVotes = value;
            }
        }

        public double Rating
        {
            get
            {
                return Math.Round((double)TotalVotes / (double)NumberOfVoters, 2);
            }
        }

        private Int32 _NumberOfVoters;
        public Int32 NumberOfVoters
        {
            get
            {
                return _NumberOfVoters;
            }
            set
            {
                _NumberOfVoters = value;
            }
        }


        private QuoteStatus _ApprovalStatus;
        public QuoteStatus ApprovalStatus
        {
            get
            {
                return _ApprovalStatus;
            }
            set
            {
                _ApprovalStatus = value;
            }
        }


        private DateTime _DateSubmitted;
        public DateTime DateSubmitted
        {
            get
            {
                return _DateSubmitted;
            }
            set
            {
                _DateSubmitted = value;
            }
        }


        private Nullable<DateTime> _DatePosted;
        public Nullable<DateTime> DatePosted
        {
            get
            {
                return _DatePosted;
            }
            set
            {
                _DatePosted = value;
            }
        }


        private CommentStatus _ThreadStatus;
        public CommentStatus CommentState
        {
            get
            {
                return _ThreadStatus;
            }
            set
            {
                _ThreadStatus = value;
            }
        }


        private Int32 _ApproveVotes;
        public Int32 ApproveVotes
        {
            get
            {
                return _ApproveVotes;
            }
            set
            {
                _ApproveVotes = value;
            }
        }


        private Int32 _DeleteVotes;
        public Int32 DeleteVotes
        {
            get
            {
                return _DeleteVotes;
            }
            set
            {
                _DeleteVotes = value;
            }
        }

        private Archive _archive;
        public Archive Archive
        {
            get
            {
                return _archive;
            }
            set
            {
                _archive = value;
            }
        }

        private Comment _LatestComment;
        public Comment LatestComment
        {
            get
            {
                return _LatestComment;
            }
            set
            {
                _LatestComment = value;
            }
        }
    }
}
